const fs = require('fs');
const axios = require('axios');

const Pictuteip = 'http://192.168.67.50:3000/image/';
/** 
 * 添加时间范围条件的SQL语句
*@param data: string, 表的列名
*@param start: string, 起始时间
*@param end: string, 结束时间
*@param and: boolean, 是否使用AND或OR连接时间范围条件
*/
function SQLTimeFrame(data, start, end, and = false) {
    let timeFrame = ``;
    if (and) {
        timeFrame = ` AND `;
    }
    timeFrame += ` ${data} BETWEEN '${start}' AND '${end}' `;
    return timeFrame;
}

/**
 * SQL执行并发送数据到前端方法
 * @param {*} ws：WebSocket实例
 * @param {*} connection ：数据库连接实例
 * @param {*} sql ：SQL语句
 * @param {*} msgtype ：消息类型
 */
function SQLExecute(ws, connection, sql, msgtype) {
    connection.query(sql, (err, results) => {
        if (err) {
            console.error("SQL执行失败:", err)
            console.error("SQL语句:", sql)
            return
        }
        SendData(ws, msgtype, results)
    })
}

/**
 * 统计数据数量的SQL语句
 * @param {*} table: 表名
 * @param {*} msgtype:消息名
 * @param {*} condition: 条件
 */
function DataCount(ws, connection, table, msgtype, condition = null,addwhere = true) {
    let sql = `SELECT COUNT(*) as count FROM ${table}`;
    if (condition) {
        if(addwhere){
            sql += ` WHERE ${condition}`;
        }else{
            sql += condition;
        }
    }
    connection.query(sql, (err, results) => {
        if (err) {
            console.error("数据统计失败:", err)
            return
        }
        SendData(ws, msgtype, results)
    })
}

/**
 * 这个方法用于向前端发送数据
 * @param {*} ws 
 * @param {*} topic：主题
 * @param {*} data ：数据
 */
function SendData(ws, topic, data) {
    let jsondate =
    {
        "topic": topic,
        "data": data
    }
    ws.send(JSON.stringify(jsondate))
}

/**
 * 发送映射名称
 * @param {*} ws 
 * @param {*} connection 
 * @param {*} prefix 
 */
function SendHeadtopic(ws, connection, prefix) {
    connection.query(`SELECT f_name,db_name FROM ${prefix} `, (err, results) => {
        if (err) {
            console.error(`查询失败: ${err}`);
            return;
        }

        if (results.length === 0) {
            console.log("没有字段可查询。");
            return;
        }

        SendData(ws, "headtitle", results)
    })

}

//#region 实时数据显示模块
function processFieldsQuery(ws, connection, query, querydata, prefix, prefix1, d_no = 0) {
    connection.query(`SELECT f_name,db_name FROM ${query}
        where type = 1`, (err, results) => {
        if (err) {
            console.error(`查询失败: ${err}`);
            return;
        }

        if (results.length === 0) {
            console.log("没有字段可查询。");
            return;
        }

        const fields = results.map(row => row.db_name);

        SendData(ws, prefix, results)

        const fieldsList = fields.map(field => `Round(AVG(${field}),2) as ${field}`).join(",");

        connection.query(`SELECT DATE_FORMAT(c_time,'%Y-%m-%d %H:%i:%s') as c_time,${fieldsList} FROM ${querydata} t 
                where d_no = ${d_no} 
                GROUP BY DATE_FORMAT(c_time,'%Y-%m-%d %H:%i:%s') order by c_time desc limit 5`, (err, results) => {
            if (err) {
                console.error("查询t_data失败:", err);
                return;
            }
            SendData(ws, prefix1, results)
        });
    });
}
//#endregion

//#region 设备，场景选择模块

/** 场景选择初始化
 *@param ws: WebSocket实例
 *@param connection: 数据库连接实例
*/
function Scenes(ws, connection, no = null) {
    sendDevices(ws, connection, "device")
    MediumQuery(ws, connection, "t_behavior_field_mapper", "t_behavior_data", null, no);
}
/**
 * @param ws: WebSocket实例
 * @param connection: 数据库连接实例
 * @param {*} type ：数据类型
 * @param {*} d_no :场景编号
 */
function ScenesQuery(ws, connection, d_type = null, d_no = null) {
    let no = d_no == -1 ? null : d_no;
    let type = d_type == -1 ? null : d_type;
    MediumQuery(ws, connection, "t_behavior_field_mapper", "t_behavior_data", type, no);
}

//设备，机房，场景列表
function sendDevices(ws, connection, prefix) {
    connection.query(`SELECT device_name,id FROM t_device`, (err, results) => {
        if (err) {
            console.error("设备列表查询失败:", err)
            return
        }
        SendData(ws, `${prefix}`, results)
    })
}
/**
 * 修改当前选择的设备
 * @param {*} ws 
 * @param {*} connection 
 * @param {*} device 
 * @param {*} no 
 */
function DeviceManager(ws, connection, device, no = 0) {
    sendDevices(ws, connection, "device")
    device.id = no;
    console.log(device);
}

function MediumQuery(ws, connection, query, querydata, type = null, d_no = null) {
    let t_type = type == null ? 'type != -1' : `type = ${type}`;
    connection.query(`SELECT f_name,db_name FROM ${query}
        where ${t_type}`, (err, results) => {
        if (err) {
            console.error(`查询失败: ${err}`);
            return;
        }

        if (results.length === 0) {
            console.log("没有字段可查询。");
            return;
        }

        const fields = results.map(row => row.db_name);

        const fieldsList = fields.map(field => `${field}`).join(",");

        let device = d_no == null ? 'd_no != -1' : `d_no = ${d_no}`;

        connection.query(`SELECT DATE_FORMAT(c_time,'%Y-%m-%d %H:%i:%s') as c_time,${fieldsList},type,d_no
            FROM ${querydata} t 
            where ${device} and ${t_type} and field5 != "" and field6 != ""
            order by c_time desc limit 10`, (err, results) => {
            if (err) {
                console.error("查询t_data失败:", err);
                return;
            }
            SendData(ws, `sence`, results)
        });
    });
}

//#region 图像识别模块
let inference_results = [];
/**
 * 图像识别处理接口
 * @param {*} connection 
 * @param {*} d_no 
 * @param {*} data 
 */
function YOLO(connection, d_no, data) {
    let timeStamp = new Date();
    Savepictures(data, timeStamp.getTime());
    return HttpSendData(data.result.split(';base64,')[1], timeStamp, d_no, connection);
    // InserPicturePath(d_no, timeStamp.getTime() + '.png', timeStamp.getTime() + 'YOLO.png', GetUTCDate(timeStamp), connection);
}

function Savepictures(data, name, default_name = "",) {
    let base64Data = data.result.split(';base64,')[1];
    // 创建Buffer对象
    let imageBuffer = Buffer.from(base64Data, 'base64');
    // 将Buffer写入文件 
    fs.writeFile('src/image/' + name + default_name + '.png', imageBuffer, (err) => {
        if (err) {
            console.error('写入图片文件时出错', err);
        } else {
            console.log('图片已成功保存');
        }
    });
}

function HttpSendData(data, timeStamp, d_no, connection) {
    return axios.post('http://localhost:4000/infer', {
        image: 'data:image/png;base64,' + data
    })
        .then(response => {
            console.log("Response Picture");
            Savepictures(response.data, timeStamp.getTime(), 'YOLO');
            inference_results = response.data.inference_results;
            console.log("YOLO:", inference_results);
            InserPicturePath(d_no, timeStamp.getTime() + '.png', timeStamp.getTime() + 'YOLO.png', GetUTCDate(timeStamp), connection);
        })
        .catch(error => {
            console.log(error);
            throw error; // 确保错误可以被调用链中的其他部分捕获
        });
}


function InserPicturePath(d_no, prename, proname, time, connection) {
    connection.query(`insert into t_behavior_data (d_no,field5,field6,field7,c_time,type) values (?,?,?,?,?,?)`, [d_no, Pictuteip + prename, Pictuteip + proname, inference_results.length ? inference_results : "", time, 2])
}
//#endregion

function GetUTCDate(timeStamp) {
    return formatLocalTime(formatUTCDate(timeStamp));
}

function formatUTCDate(timeStamp) {
    let localDate = new Date(
        timeStamp.getFullYear(),
        timeStamp.getMonth(),
        timeStamp.getDate(),
        timeStamp.getHours(),
        timeStamp.getMinutes(),
        timeStamp.getSeconds()
    );
    return localDate;
}

function formatLocalTime(date) {
    const pad = n => n.toString().padStart(2, '0');
    return `${date.getFullYear()}-${pad(date.getMonth() + 1)}-${pad(date.getDate())} ` +
        `${pad(date.getHours())}:${pad(date.getMinutes())}:${pad(date.getSeconds())}`;
}

//#endregion

//#region 指令配置模块
//页面初始化
function InitOrder(ws, connection, d_no = 0) {
    connection.query("SELECT id,ref_id FROM t_direct_config where ref_id is null", (err, results) => {
        if (err) {
            console.error("t_direct_config - id查询失败:", err)
            return
        }

        results.forEach(async (row) => {
            let level = 0;
            let results_child = [];
            await Seachchild(row.id, ws, 0, level, connection, results_child, d_no);
            console.log(results_child);
            SendData(ws, `t_direct_config`, results_child);
        })
    })

}

// 递归查询子节点
async function Seachchild(id, ws, f_id, level, connection, results_child, d_no) {
    return new Promise((resolve, reject) => {
        connection.query(`select id,ref_id from t_direct_config where ref_id = ${id} order by id`, (err, results) => {
            if (err) {
                console.error("t_direct_config - value 查询失败:", err);
                return reject(err);
            }

            connection.query('select con.*,dir.`value` from t_direct_config con,t_direct dir where con.id = ? And con.id=dir.config_id AND (con.d_no = ? OR con.d_no = -1)', [id, d_no], (err, configResults) => {
                if (err) {
                    console.error("t_direct_config - value 查询失败:", err);
                    return reject(err);
                }
                if (configResults.length == 0) return resolve();

                let child = results.length !== 0 ? 1 : null;

                configResults.forEach((row) => {
                    row.level = level;
                    row.child = child;
                });

                results_child.push(configResults[0]);

                if (results.length !== 0) {
                    const promises = results.map(row => Seachchild(row.id, ws, id, level + 1, connection, results_child, d_no));
                    Promise.all(promises).then(() => resolve()).catch(reject);
                } else {
                    resolve();
                }
            });
        });
    });
}

//更新or插入指令数据
function UpdateOrder(msg, connection, mqttClientInstance, d_no = 0) {

    let msg_data = JSON.parse(msg);
    connection.query(`update t_direct set value =? where config_id =? `, [msg_data.value, msg_data.config_id,], (err) => {
        if (err) {
            console.error("t_direct 更新失败:", err)
        }
        console.log("success-update-page5")
        connection.query(`SELECT fields FROM t_direct_config WHERE id = ?`, [msg_data.config_id], (err, results) => {
            if (err) {
                console.error("t_direct_config - value 查询失败:", err)
                return
            }

            let data = msg_data.value;
            let message = { [results[0].fields]: data };

            console.log(message);
            //mqtt 发消息
            mqttClientInstance.publish(
                "sensorData", message
            );
        })
    })

    connection.query(`insert into t_operation_log (d_no,value,c_time) values (?,?,?)`, [d_no, msg_data.log, GetUTCDate(new Date())], (err) => {
        if (err) {
            console.error("t_operation_log 插入失败:", err)
        }
        console.log("success-insert-log")
    })
}

//数据初始化
function InitData(connection) {
    //将config_id 为 1，3，4，5的value值设置为默认值stop
    let config_id = [1, 3, 4, 5];
    config_id.forEach((id) => {
        connection.query(`update t_direct set value =? where config_id =? `, ["stop", id], (err) => {
            if (err) {
                console.error("t_direct 更新失败:", err)
            }
            else {
                console.log("success-init-data")
            }
        })
    })
}

//#endregion

module.exports = {
    SQLTimeFrame,
    SendData,
    UpdateOrder,
    InitData,
    InitOrder,
    Seachchild,
    processFieldsQuery,
    Scenes,
    ScenesQuery,
    DataCount,
    SQLExecute,
    DeviceManager,
    SendHeadtopic,
    sendDevices,
    YOLO
}